﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Globalization;

namespace Reviewer.Data
{
    [CLSCompliant(false)]
    public class NewsletterRepository : Bases.RepositoryBase<String>
    {
        #region fields (private)
        private const string TABLE_NAME = "V2_Newsletter";
        private static readonly CrudColumnInfo columnId = new CrudColumnInfo(0, "Id", MySqlDbType.Int32, true, false, false, true);
        private static readonly CrudColumnInfo columnEmail = new CrudColumnInfo(1, "email", MySqlDbType.VarChar, 255, false, false, true);
        #endregion

        public NewsletterRepository() : base() { }

        public bool Exists(string email)
        {
            using (MySqlCommand command = new MySqlCommand())
            {
                command.CommandText = String.Format(CultureInfo.InvariantCulture,
                    "SELECT {1},{2} FROM {0} WHERE {2} = @{2}"
                    , NewsletterRepository.TABLE_NAME
                    , NewsletterRepository.columnId.Name
                    , NewsletterRepository.columnEmail.Name);
                NewsletterRepository.columnEmail.AddParameter(command, email);

                return !String.IsNullOrEmpty(base.ExecuteReaderAndBuildItem(command));
            }
        }

        public bool Create(string email)
        {
            using (MySqlCommand command = new MySqlCommand())
            {
                command.CommandText = String.Format(CultureInfo.InvariantCulture,
                    "INSERT INTO {0} ({1}) VALUES (@{1})"
                    , NewsletterRepository.TABLE_NAME
                    , NewsletterRepository.columnEmail.Name);
                NewsletterRepository.columnEmail.AddParameter(command, email);

                return base.ExecuteNonQuery(command) > 0;
            }
        }

        protected override string BuildItemFromReader(System.Data.IDataReader reader)
        {
            return reader.GetString(1);
        }

        public bool Delete(string email)
        {
            using (MySqlCommand command = new MySqlCommand())
            {
                command.CommandText = String.Format(CultureInfo.InvariantCulture,
                    "DELETE FROM {0} WHERE {1} = @{1}"
                    , NewsletterRepository.TABLE_NAME
                    , NewsletterRepository.columnEmail.Name);
                NewsletterRepository.columnEmail.AddParameter(command, email);

                return base.ExecuteNonQuery(command) > 0;
            }
        }
    }
}
